from typing import Any, Dict, List, Optional, Type, TypeVar, Generic, Union, Tuple
import json
from sqlalchemy import inspect, asc, desc, cast, Integer, Float, String
from app.models.database import get_db, create_tables
from app.services.db_service_interface import DBServiceInterface
from app.core.config import settings
import psycopg2 

T = TypeVar('T')

class PostgresService(DBServiceInterface[T]):
    """PostgreSQL服务类，提供与SQLiteService相同的接口"""
    def __init__(self):
        print("初始化Postgres服务")
        # 尝试创建数据库
        self.create_database()
        # 创建表
        create_tables()
        self.connected = True
        
    def create_database(self):
        """创建数据库（如果不存在）"""
        try:
            # 连接到默认的 postgres 数据库
            conn = psycopg2.connect(
                host=settings.POSTGRES_SERVER,
                port=settings.POSTGRES_PORT,
                user=settings.POSTGRES_USER,
                password=settings.POSTGRES_PASSWORD,
                database="postgres"  # 连接到默认数据库
            )
            conn.autocommit = True  # 设置自动提交
            cursor = conn.cursor()
            
            # 检查数据库是否存在
            cursor.execute(f"SELECT 1 FROM pg_database WHERE datname = '{settings.POSTGRES_DB}'")
            exists = cursor.fetchone()
            
            if not exists:
                # 创建数据库
                cursor.execute(f"CREATE DATABASE {settings.POSTGRES_DB}")
                print(f"数据库 {settings.POSTGRES_DB} 创建成功")
            
            cursor.close()
            conn.close()
        except Exception as e:
            print(f"创建数据库失败: {str(e)}")
    def save_entity(self, entity_class: Type[T], entity_data: Dict[str, Any], 
                   primary_key_value: Any = None, primary_key_field: str = None) -> Optional[Dict[str, Any]]:
        """保存实体到PostgreSQL
        
        Args:
            entity_class: 实体类
            entity_data: 实体数据
            primary_key_value: 主键值，用于查找现有实体
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            保存的实体对象的字典表示
        """
        db = get_db()
        
        try:
            # 预处理数据，将字典和列表转换为JSON字符串
            processed_data = {}
            for key, value in entity_data.items():
                if isinstance(value, (dict, list)):
                    processed_data[key] = json.dumps(value, ensure_ascii=False)
                else:
                    processed_data[key] = value
            
            # 如果未指定主键字段，自动检测
            if primary_key_field is None:
                mapper = inspect(entity_class)
                primary_key_field = mapper.primary_key[0].name
            
            # 如果提供了主键值，查找现有实体
            entity = None
            if primary_key_value is not None:
                entity = db.query(entity_class).filter(
                    getattr(entity_class, primary_key_field) == primary_key_value
                ).first()
            else:
                # 检查是否在实体数据中提供了主键值
                primary_key_value = processed_data.get(primary_key_field)
                if primary_key_value is not None:
                    entity = db.query(entity_class).filter(
                        getattr(entity_class, primary_key_field) == primary_key_value
                    ).first()
            
            if entity:
                # 更新现有实体
                for key, value in processed_data.items():
                    if hasattr(entity, key):
                        setattr(entity, key, value)
            else:
                # 创建新实体
                if primary_key_value is not None and primary_key_field in processed_data:
                    processed_data[primary_key_field] = primary_key_value
                entity = entity_class(**processed_data)
                db.add(entity)
            
            db.commit()
            db.refresh(entity)
            
            # 将实体转换为字典再返回
            result = {}
            for column in entity.__table__.columns:
                value = getattr(entity, column.name)
                # 尝试解析JSON字符串
                if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                    try:
                        value = json.loads(value)
                    except:
                        pass
                result[column.name] = value
            
            return result
        except Exception as e:
            print(f"保存实体到PostgreSQL失败: {str(e)}")
            db.rollback()
            return None
    
    def save_entity_by_filter(self,entity_class: Type[T], entity_data: Dict[str, Any],  
                            filters: Dict[str, Any]) -> Optional[T]:
        """按条件保存实体到PostgreSQL
        
        Args:
            entity_data: 实体数据字典
            entity_class: 实体类
            filters: 过滤条件，格式为 {字段名: 值}
            
        Returns:
            保存后的实体对象或None
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            for field, value in filters.items():
                if hasattr(entity_class, field):
                    query = query.filter(getattr(entity_class, field) == value)
            
            # 查询现有实体
            entity = query.first()
            
            # 预处理数据，将字典和列表转换为JSON字符串
            processed_data = {}
            for key, value in entity_data.items():
                if isinstance(value, (dict, list)):
                    processed_data[key] = json.dumps(value, ensure_ascii=False)
                else:
                    processed_data[key] = value
            
            if entity:
                # 更新现有实体
                for key, value in processed_data.items():
                    if hasattr(entity, key):
                        setattr(entity, key, value)
                db.commit()
                return entity
            else:
                # 创建新实体
                entity = entity_class(**processed_data)
                db.add(entity)
                db.commit()
                db.refresh(entity)
                return entity
        except Exception as e:
            print(f"按条件保存实体到PostgreSQL失败: {str(e)}")
            db.rollback()
            return None
    
    def save_entities(self,entity_class: Type[T], entities_data: List[Dict[str, Any]]) -> List[Optional[T]]:
        """批量保存实体到PostgreSQL
        
        Args:
            entities_data: 实体数据字典列表
            entity_class: 实体类
            
        Returns:
            保存后的实体对象列表
        """
        db = get_db()
        
        try:
            entities = []
            
            for entity_data in entities_data:
                # 预处理数据，将字典和列表转换为JSON字符串
                processed_data = {}
                for key, value in entity_data.items():
                    if isinstance(value, (dict, list)):
                        processed_data[key] = json.dumps(value, ensure_ascii=False)
                    else:
                        processed_data[key] = value
                
                # 创建实体
                entity = entity_class(**processed_data)
                db.add(entity)
                entities.append(entity)
            
            db.commit()
            
            # 刷新实体以获取自动生成的ID
            for entity in entities:
                db.refresh(entity)
            
            # 将实体转换为字典
            results = []
            for entity in entities:
                result = {}
                for column in entity.__table__.columns:
                    value = getattr(entity, column.name)
                    # 尝试解析JSON字符串
                    if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                        try:
                            value = json.loads(value)
                        except:
                            pass
                    result[column.name] = value
                results.append(result)
            
            return results
        except Exception as e:
            print(f"批量保存实体到PostgreSQL失败: {str(e)}")
            db.rollback()
            return [None] * len(entities_data)
    
    
    def get_entity(self, entity_class: Type[T], primary_key_value: Any, 
                  primary_key_field: str = None) -> Optional[T]:
        """获取实体
        
        Args:
            entity_class: 实体类
            primary_key_value: 主键值
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            实体对象或None
        """
        db = get_db()
        
        try:
            # 如果未指定主键字段，自动检测
            if primary_key_field is None:
                mapper = inspect(entity_class)
                primary_key_field = mapper.primary_key[0].name
            
            entity = db.query(entity_class).filter(
                getattr(entity_class, primary_key_field) == primary_key_value
            ).first()
            
            return entity
        except Exception as e:
            print(f"获取实体失败: {str(e)}")
            return None
    
    def get_entity_as_dict(self, entity_class: Type[T], primary_key_value: Any, 
                          primary_key_field: str = None) -> Optional[Dict[str, Any]]:
        """获取实体并转换为字典
        
        Args:
            entity_class: 实体类
            primary_key_value: 主键值
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            实体字典或None
        """
        entity = self.get_entity(entity_class, primary_key_value, primary_key_field)
        if not entity:
            return None
        
        # 将实体转换为字典
        result = {}
        for column in entity.__table__.columns:
            value = getattr(entity, column.name)
            # 尝试解析JSON字符串
            if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                try:
                    value = json.loads(value)
                except:
                    pass
            result[column.name] = value
        
        return result
    
    def delete_entity(self, entity_class: Type[T], primary_key_value: Any, 
                     primary_key_field: str = None) -> bool:
        """删除实体
        
        Args:
            entity_class: 实体类
            primary_key_value: 主键值
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            是否成功删除
        """
        db = get_db()
        
        try:
            # 如果未指定主键字段，自动检测
            if primary_key_field is None:
                mapper = inspect(entity_class)
                primary_key_field = mapper.primary_key[0].name
            
            entity = db.query(entity_class).filter(
                getattr(entity_class, primary_key_field) == primary_key_value
            ).first()
            
            if entity:
                db.delete(entity)
                db.commit()
                return True
            return False
        except Exception as e:
            print(f"删除实体失败: {str(e)}")
            db.rollback()
            return False
    
    def delete_entities_by_filter(self, entity_class: Type[T], filters: Dict[str, Any]) -> int:
        """按条件删除多个实体
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，格式为 {字段名: 值}
            
        Returns:
            删除的实体数量
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            if filters:
                for field, value in filters.items():
                    if hasattr(entity_class, field):
                        query = query.filter(getattr(entity_class, field) == value)
            
            # 获取符合条件的实体数量
            entities = query.all()
            count = len(entities)
            
            # 逐个删除实体，以触发级联删除
            for entity in entities:
                db.delete(entity)
            
            db.commit()
            
            return count
        except Exception as e:
            print(f"按条件删除实体失败: {str(e)}")
            db.rollback()
            return 0
    
    def _parse_order_by(self, entity_class: Type[T], order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]]) -> List:
        """解析排序参数
        
        Args:
            entity_class: 实体类
            order_by: 排序条件
            
        Returns:
            SQLAlchemy 排序表达式列表
        """
        if not order_by:
            return []
            
        order_expressions = []
        
        # 统一转换为列表格式
        if isinstance(order_by, str):
            order_list = [order_by]
        elif isinstance(order_by, tuple):
            order_list = [order_by]
        else:
            order_list = order_by
            
        for item in order_list:
            if isinstance(item, str):
                # 字符串格式，默认正序
                field_expression = item
                direction = "asc"
            elif isinstance(item, tuple) and len(item) == 2:
                # 元组格式 (字段表达式, 方向)
                field_expression, direction = item
            else:
                continue
                
            # 解析字段表达式，支持类型转换
            field_attr = self._parse_field_expression(entity_class, field_expression)
            if field_attr is None:
                continue
            
            # 应用排序方向
            if direction.lower() == "desc":
                order_expressions.append(desc(field_attr))
            else:  # 默认正序
                order_expressions.append(asc(field_attr))
                
        return order_expressions
    
    def _parse_field_expression(self, entity_class: Type[T], field_expression: str):
        """解析字段表达式，支持类型转换
        
        Args:
            entity_class: 实体类
            field_expression: 字段表达式，如 "field_name" 或 "field_name::int"
            
        Returns:
            SQLAlchemy 字段表达式或 None
        """
        # 检查是否包含类型转换
        if '::' in field_expression:
            field_name, cast_type = field_expression.split('::', 1)
            field_name = field_name.strip()
            cast_type = cast_type.strip().lower()
        else:
            field_name = field_expression.strip()
            cast_type = None
            
        # 检查字段是否存在
        if not hasattr(entity_class, field_name):
            return None
            
        field_attr = getattr(entity_class, field_name)
        
        # 应用类型转换
        if cast_type:
            if cast_type in ['int', 'integer']:
                field_attr = cast(field_attr, Integer)
            elif cast_type in ['float', 'real', 'numeric']:
                field_attr = cast(field_attr, Float)
            elif cast_type in ['str', 'string', 'text']:
                field_attr = cast(field_attr, String)
            # 可以根据需要添加更多类型转换
            
        return field_attr

    def _apply_filters(self, query, entity_class: Type[T], filters):
        """应用过滤条件到查询
        
        Args:
            query: SQLAlchemy 查询对象
            entity_class: 实体类
            filters: 过滤条件，支持统一结构或传统结构
            
        Returns:
            应用过滤条件后的查询对象
        """
        from sqlalchemy import or_, and_
        
        # 检测并转换为统一格式
        unified_filter = self._convert_to_unified_format(filters)
        
        # 处理统一格式的过滤条件
        if isinstance(unified_filter, dict) and "conditions" in unified_filter:
            condition_expr = self._build_unified_condition(entity_class, unified_filter)
            if condition_expr is not None:
                query = query.filter(condition_expr)
        
        return query
    
    def _convert_to_unified_format(self, filters):
        """将传统格式转换为统一格式"""
        if not isinstance(filters, dict):
            return filters
            
        # 如果已经是统一格式，直接返回
        if "conditions" in filters and "logic" in filters:
            return filters
            
        # 转换传统格式
        conditions = []
        
        for field, condition in filters.items():
            if field == "_or":
                # 转换 _or 格式
                if isinstance(condition, list):
                    or_conditions = []
                    for or_filter in condition:
                        if isinstance(or_filter, dict):
                            for or_field, or_condition in or_filter.items():
                                or_conditions.append({
                                    "type": "field",
                                    "field": or_field,
                                    "condition": or_condition
                                })
                    if or_conditions:
                        conditions.append({
                            "type": "group",
                            "conditions": or_conditions,
                            "logic": "or"
                        })
            elif field == "_keyword":
                # 转换 _keyword 格式
                if isinstance(condition, dict) and "value" in condition and "fields" in condition:
                    conditions.append({
                        "type": "keyword",
                        "value": condition["value"],
                        "fields": condition["fields"]
                    })
            else:
                # 普通字段条件
                if isinstance(condition, dict) and "conditions" in condition:
                    # 已经是新的统一语法
                    field_conditions = []
                    for single_condition in condition["conditions"]:
                        field_conditions.append({
                            "type": "field",
                            "field": field,
                            "condition": single_condition
                        })
                    conditions.append({
                        "type": "group",
                        "conditions": field_conditions,
                        "logic": condition.get("logic", "and")
                    })
                elif isinstance(condition, list):
                    # 兼容旧语法：列表表示AND条件
                    field_conditions = []
                    for single_condition in condition:
                        field_conditions.append({
                            "type": "field",
                            "field": field,
                            "condition": single_condition
                        })
                    conditions.append({
                        "type": "group",
                        "conditions": field_conditions,
                        "logic": "and"
                    })
                else:
                    # 单个条件
                    conditions.append({
                        "type": "field",
                        "field": field,
                        "condition": condition
                    })
        
        return {
            "conditions": conditions,
            "logic": "and"
        }
    
    def _build_unified_condition(self, entity_class: Type[T], filter_group):
        """构建统一格式的查询条件"""
        from sqlalchemy import or_, and_
        
        if not isinstance(filter_group, dict) or "conditions" not in filter_group:
            return None
            
        conditions = filter_group["conditions"]
        logic = filter_group.get("logic", "and").lower()
        
        condition_exprs = []
        
        for condition_item in conditions:
            condition_type = condition_item.get("type", "field")
            
            if condition_type == "field":
                # 字段条件
                field = condition_item.get("field")
                condition = condition_item.get("condition")
                
                if field and hasattr(entity_class, field):
                    field_attr = getattr(entity_class, field)
                    condition_expr = self._build_condition(field_attr, condition)
                    if condition_expr is not None:
                        condition_exprs.append(condition_expr)
                        
            elif condition_type == "group":
                # 嵌套条件组
                nested_expr = self._build_unified_condition(entity_class, condition_item)
                if nested_expr is not None:
                    condition_exprs.append(nested_expr)
                    
            elif condition_type == "keyword":
                # 关键词搜索
                keyword = condition_item.get("value")
                search_fields = condition_item.get("fields", [])
                
                if keyword and search_fields:
                    search_pattern = f"%{keyword}%"
                    keyword_conditions = []
                    
                    for search_field in search_fields:
                        if hasattr(entity_class, search_field):
                            field_attr = getattr(entity_class, search_field)
                            keyword_conditions.append(field_attr.ilike(search_pattern))
                    
                    if keyword_conditions:
                        condition_exprs.append(or_(*keyword_conditions))
        
        if not condition_exprs:
            return None
            
        if len(condition_exprs) == 1:
            return condition_exprs[0]
            
        if logic == "or":
            return or_(*condition_exprs)
        else:
            return and_(*condition_exprs)
    
    def _build_condition(self, field_attr, condition):
        """构建单个字段的查询条件"""
        if isinstance(condition, dict):
            # 复杂条件
            if "like" in condition:
                return field_attr.like(condition["like"])
            elif "ilike" in condition:
                return field_attr.ilike(condition["ilike"])
            elif "op" in condition and "value" in condition:
                op = condition["op"]
                value = condition["value"]
                if op == ">":
                    return field_attr > value
                elif op == ">=":
                    return field_attr >= value
                elif op == "<":
                    return field_attr < value
                elif op == "<=":
                    return field_attr <= value
                elif op == "!=":
                    return field_attr != value
                elif op == "in":
                    return field_attr.in_(value)
                elif op == "not_in":
                    return ~field_attr.in_(value)
                elif op == "is_null":
                    return field_attr.is_(None)
                elif op == "is_not_null":
                    return field_attr.isnot(None)
        else:
            # 简单的精确匹配
            return field_attr == condition
        
        return None

    def query_entities(self, entity_class: Type[T], filters: Dict[str, Any] = None, 
                      limit: int = None, offset: int = None, 
                      order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> List[T]:
        """查询实体列表
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，支持以下格式：
                - 精确匹配: {字段名: 值}
                - LIKE 通配符: {字段名: {"like": "pattern"}}
                - ILIKE 不区分大小写: {字段名: {"ilike": "pattern"}}
                - 其他操作符: {字段名: {"op": ">", "value": 100}}
                - OR 条件: {"_or": [{"field1": "value1"}, {"field2": {"like": "pattern%"}}]}
                - 关键词多字段搜索: {"_keyword": {"value": "search_term", "fields": ["field1", "field2"]}}
            limit: 限制返回数量
            offset: 跳过记录数
            order_by: 排序条件，支持以下格式：
                - 字符串: "field_name" (默认正序)
                - 元组: ("field_name", "asc"|"desc")
                - 列表: [("field1", "asc"), ("field2", "desc"), "field3"]
                - 类型转换: "field_name::int", ("field_name::float", "desc")
                  支持的类型: int/integer, float/real/numeric, str/string/text
            
        Returns:
            实体列表
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            if filters:
                query = self._apply_filters(query, entity_class, filters)
            
            # 应用排序
            order_expressions = self._parse_order_by(entity_class, order_by)
            for expr in order_expressions:
                query = query.order_by(expr)
            
            # 应用分页
            if offset is not None:
                query = query.offset(offset)
            if limit is not None:
                query = query.limit(limit)
            
            return query.all()
        except Exception as e:
            print(f"查询实体列表失败: {str(e)}")
            return []
    
    def query_entities_as_dicts(self, entity_class: Type[T], filters: Dict[str, Any] = None, 
                              limit: int = None, offset: int = None, 
                              order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> List[Dict[str, Any]]:
        """查询实体列表并转换为字典列表
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，支持以下格式：
                - 精确匹配: {字段名: 值}
                - LIKE 通配符: {字段名: {"like": "pattern"}}
                - ILIKE 不区分大小写: {字段名: {"ilike": "pattern"}}
                - 其他操作符: {字段名: {"op": ">", "value": 100}}
                - OR 条件: {"_or": [{"field1": "value1"}, {"field2": {"like": "pattern%"}}]}
                - 关键词多字段搜索: {"_keyword": {"value": "search_term", "fields": ["field1", "field2"]}}
            limit: 限制返回数量
            offset: 跳过记录数
            order_by: 排序条件，支持以下格式：
                - 字符串: "field_name" (默认正序)
                - 元组: ("field_name", "asc"|"desc")
                - 列表: [("field1", "asc"), ("field2", "desc"), "field3"]
                - 类型转换: "field_name::int", ("field_name::float", "desc")
                  支持的类型: int/integer, float/real/numeric, str/string/text
        Returns:
            字典列表
        """
        entities = self.query_entities(entity_class, filters, limit, offset, order_by)

        
        result = []
        for entity in entities:
            entity_dict = {}
            for column in entity.__table__.columns:
                value = getattr(entity, column.name)
                # 尝试解析JSON字符串
                if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                    try:
                        value = json.loads(value)
                    except:
                        pass
                entity_dict[column.name] = value
            result.append(entity_dict)
        
        return result
    
    def count_entities(self, entity_class: Type[T], filters: Dict[str, Any] = None) -> int:
        """统计实体数量
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，格式与query_entities相同
            
        Returns:
            符合条件的实体数量
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            if filters:
                query = self._apply_filters(query, entity_class, filters)
            
            return query.count()
        except Exception as e:
            print(f"统计实体数量失败: {str(e)}")
            return 0
    
    def query_entities_as_dicts_by_page(self, entity_class: Type[T], filters: Dict[str, Any] = None,
                                       page: int = 1, pageSize: int = 10,
                                       orderBy: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> Dict[str, Any]:
        """分页查询实体列表并返回完整分页信息
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，格式与query_entities相同
            page: 页码，从1开始
            pageSize: 每页大小
            orderBy: 排序条件，格式与query_entities相同
            
        Returns:
            包含分页信息的字典：
            {
                "items": [...],        # 当前页数据
                "total": 100,          # 总记录数
                "page": 1,             # 当前页码
                "pageSize": 10,       # 每页大小
                "totalPages": 10      # 总页数
            }
        """
        # 确保页码和页大小有效
        page = max(1, page)
        pageSize = max(1, pageSize)
        
        # 计算偏移量
        offset = (page - 1) * pageSize
        
        # 获取总数
        total = self.count_entities(entity_class, filters)
        
        # 计算总页数
        total_pages = (total + pageSize - 1) // pageSize if total > 0 else 0
        
        # 获取当前页数据
        items = self.query_entities_as_dicts(entity_class, filters, pageSize, offset, orderBy)
        
        return {
            "items": items,
            "total": total,
            "page": page,
            "pageSize": pageSize,
            "totalPages": total_pages
        }

    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            get_db()
            return True
        except Exception as e:
            print(f"PostgreSQL连接测试失败: {str(e)}")
            return False


# 只有当DB_TYPE设置为postgresql时才创建实例
if settings.DB_TYPE.lower() == "postgresql":
    postgres_service = PostgresService()
    print(f"PostgreSQL连接状态: {'成功' if postgres_service.test_connection() else '失败'}")
else:
    # 创建一个空实例，避免导入错误
    postgres_service = None